﻿Imports System.Data.SqlClient

Public Class frmImportarOperadores
    Dim ConnectionACG As String
    Dim ConnectionAUSL As String
    Dim ConnectionASP As String
    Dim ConnectionLCB As String
    Dim ConnectionAvailable As String
    Dim Registros As Integer
    Dim Actual As Integer
    Dim Operador As New clsOperadoresGlobal
    Private Sub frmImportarOperadores_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ProgressBar1.Visible = False

        Dim BD_ACG As String = "ctNOM2013ACG"
        Dim BD_AUSL As String = "ctNOM2013AUSL"
        Dim BD_ASP As String = "ctNOM2011ASP"
        Dim BD_LCB As String = "ctNOM2013LCB"

        Registros = 0

        ConnectionACG = "Server=" & Server.Trim & "\COMPAC;Database=" & BD_ACG & "; user id = SA; password = Compac123;"
        ConnectionAUSL = "Server=" & Server.Trim & "\COMPAC;Database=" & BD_AUSL & "; user id = SA; password = Compac123;"
        ConnectionASP = "Server=" & Server.Trim & "\COMPAC;Database=" & BD_ASP & "; user id = SA; password = Compac123;"
        ConnectionLCB = "Server=" & Server.Trim & "\COMPAC;Database=" & BD_LCB & "; user id = SA; password = Compac123;"

        ConnectionAvailable = ConnectionACG
        Registros = Registros + BuscarIntDato("SELECT COUNT(*) FROM NOM10001 WHERE IdPuesto=9")

        ConnectionAvailable = ConnectionAUSL
        Registros = Registros + BuscarIntDato("SELECT COUNT(*) FROM NOM10001 WHERE IdPuesto=60")

        ConnectionAvailable = ConnectionASP
        Registros = Registros + BuscarIntDato("SELECT COUNT(*) FROM NOM10001 WHERE IdPuesto=2")

        ConnectionAvailable = ConnectionLCB
        Registros = Registros + BuscarIntDato("SELECT COUNT(*) FROM NOM10001 WHERE IdPuesto=98")





    End Sub

    Private Sub RadButton16_Click(sender As System.Object, e As System.EventArgs) Handles RadButton16.Click
        Close()

    End Sub
    Friend Function BuscarIntDato(ByVal strTabla As String, ByVal strCampo As String, ByVal strWhere As String) As String
        Dim cnnConn As New SqlConnection
        Dim intRetVal As Integer

        Try
            cnnConn.ConnectionString = ConnectionAvailable
            cnnConn.Open()

            Dim cmdBuscar As New SqlCommand
            cmdBuscar.CommandText = "Select " & strCampo & " From " & strTabla & " Where " & strWhere
            cmdBuscar.Connection = cnnConn

            intRetVal = cmdBuscar.ExecuteScalar

        Catch ex As SqlException
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cnnConn.State = ConnectionState.Open Then
                cnnConn.Close()
            End If
        End Try

        If intRetVal = Nothing Then
            Return 0
        Else
            Return intRetVal
        End If

    End Function

    Friend Function BuscarIntDato(ByVal strSqlQry As String) As String
        Dim cnnConn As New SqlConnection
        Dim intRetVal As Integer

        Try

            cnnConn.ConnectionString = ConnectionAvailable
            cnnConn.Open()

            Dim cmdBuscar As New SqlCommand
            cmdBuscar.CommandText = strSqlQry
            cmdBuscar.Connection = cnnConn

            If Not IsDBNull(cmdBuscar.ExecuteScalar) Then
                intRetVal = cmdBuscar.ExecuteScalar
            Else
                intRetVal = 0
            End If


        Catch ex As SqlException
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally
            If cnnConn.State = ConnectionState.Open Then
                cnnConn.Close()
            End If
        End Try

        If intRetVal = Nothing Then
            Return 0
        Else
            Return intRetVal
        End If

    End Function

    Private Sub RadButton21_Click(sender As System.Object, e As System.EventArgs) Handles RadButton21.Click
        Dim Resp As Integer = MsgBox("El proceso puede tardar varios minutos, ¿Deseas continuar?", MsgBoxStyle.YesNo, "Aviso")
        If Resp <> 6 Then
            Close()
        Else
            Comenzar()
        End If
    End Sub
    Sub Comenzar()
        With ProgressBar1
            .Minimum = 0
            .Maximum = Registros
            .Value = 0
            .Visible = True
        End With

        Actual = 0
        CargaACG()
        CargaAUSL()
        CargaASP()
        CargaLCB()
        Close()


    End Sub
    Sub CargaACG()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = ConnectionACG

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From NOM10001 WHERE IdPuesto=9 " 

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdOperador As Integer = 0

        Do While rdBuscar.Read()
            Operador.IDEmpleado = rdBuscar("IdEmpleado")
            Operador.Codigo = rdBuscar("CodigoEmpleado").ToString.Trim
            Operador.Nombre = rdBuscar("NombreLargo").ToString.Trim
            Operador.EstadoEmpleado = rdBuscar("EstadoEmpleado").ToString.Trim
            Operador.Empresa = "ACG"
            IdOperador = BuscarIntDatoCitra("OperadoresGlobal", "IdOperador", "IdEmpleado=" & Operador.IDEmpleado & " AND Empresa='" & Operador.Empresa & "'")
            If IdOperador > 0 Then
                Operador.IdOperador = IdOperador
                Operador.Actualizar()
            Else
                Operador.Insertar()
            End If
            Actual = Actual + 1
            ProgressBar1.Value = Actual
            Application.DoEvents()
        Loop
    End Sub
    Sub CargaAUSL()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = ConnectionAUSL

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From NOM10001 WHERE IdPuesto=60 "


        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdOperador As Integer = 0

        Do While rdBuscar.Read()
            Operador.IDEmpleado = rdBuscar("IdEmpleado")
            Operador.Codigo = rdBuscar("CodigoEmpleado").ToString.Trim
            Operador.Nombre = rdBuscar("NombreLargo").ToString.Trim
            Operador.EstadoEmpleado = rdBuscar("EstadoEmpleado").ToString.Trim
            Operador.Empresa = "AUSL"
            IdOperador = BuscarIntDatoCitra("OperadoresGlobal", "IdOperador", "IdEmpleado=" & Operador.IDEmpleado & " AND Empresa='" & Operador.Empresa & "'")
            If IdOperador > 0 Then
                Operador.IdOperador = IdOperador
                Operador.Actualizar()

            Else
                Operador.Insertar()
            End If
            Actual = Actual + 1
            ProgressBar1.Value = Actual
            Application.DoEvents()
        Loop
    End Sub
    Sub CargaASP()
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = ConnectionASP

        Dim cmdBuscar As New SqlCommand
        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From NOM10001 WHERE IdPuesto=2 "

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdOperador As Integer = 0

        Do While rdBuscar.Read()
            Operador.IDEmpleado = rdBuscar("IdEmpleado")
            Operador.Codigo = rdBuscar("CodigoEmpleado").ToString.Trim
            Operador.Nombre = rdBuscar("NombreLargo").ToString.Trim
            Operador.EstadoEmpleado = rdBuscar("EstadoEmpleado").ToString.Trim
            Operador.Empresa = "ASP"
            IdOperador = BuscarIntDatoCitra("OperadoresGlobal", "IdOperador", "IdEmpleado=" & Operador.IDEmpleado & " AND Empresa='" & Operador.Empresa & "'")
            If IdOperador > 0 Then
                Operador.IdOperador = IdOperador
                Operador.Actualizar()
            Else
                Operador.Insertar()
            End If
            Actual = Actual + 1
            ProgressBar1.Value = Actual
            Application.DoEvents()
        Loop
    End Sub
    Sub CargaLCB()

        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = ConnectionLCB

        Dim cmdBuscar As New SqlCommand

        cmdBuscar.Connection = cnConn
        cmdBuscar.CommandText = "SELECT * From NOM10001 WHERE IdPuesto=98 "

        Dim rdBuscar As SqlDataReader

        cnConn.Open()
        rdBuscar = cmdBuscar.ExecuteReader

        Dim IdOperador As Integer = 0

        Do While rdBuscar.Read()
            Operador.IDEmpleado = rdBuscar("IdEmpleado")
            Operador.Codigo = rdBuscar("CodigoEmpleado").ToString.Trim
            Operador.Nombre = rdBuscar("NombreLargo").ToString.Trim
            Operador.EstadoEmpleado = rdBuscar("EstadoEmpleado").ToString.Trim
            Operador.Empresa = "LCB"
            IdOperador = BuscarIntDatoCitra("OperadoresGlobal", "IdOperador", "IdEmpleado=" & Operador.IDEmpleado & " AND Empresa='" & Operador.Empresa & "'")
            If IdOperador > 0 Then
                Operador.IdOperador = IdOperador
                Operador.Actualizar()
            Else
                Operador.Insertar()
            End If
            Actual = Actual + 1
            ProgressBar1.Value = Actual
            Application.DoEvents()
        Loop

    End Sub
End Class